package cn.com.headfree.tjfx.service.impl;

import cn.com.headfree.base.dao.SysDictionaryInfoDao;
import cn.com.headfree.entity.*;
import cn.com.headfree.tjfx.dao.IStatisticalDao;
import cn.com.headfree.tjfx.dto.AgeStructureDto;
import cn.com.headfree.tjfx.dto.StaffRankDto;
import cn.com.headfree.tjfx.service.IStatisticalService;
import cn.com.headfree.util.ExcelExportUtil;
import cn.com.headfree.util.SysDictionaryUtil;
import com.gta.edu.sdk.common.dto.Page;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoUnit;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @Description: TODO
 * @Author wcSun
 * @Date 2021/9/29
 **/
@Service("statisticalService")
public class StatisticalServiceImpl implements IStatisticalService {

    private static final int manRetireAge = 60;

    private static final int womanRetireAge = 55;

    @Autowired
    private IStatisticalDao statisticalDao;

    @Autowired
    private SysDictionaryInfoDao sysDictionaryInfoDao;

    private DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");


    /**
     * 信息总览-年龄结构分布
     * @param orgId
     * @return
     */
    @Override
    public Object aChartAge(String orgId) {
        List<IscUser> userList = statisticalDao.searchUserList(orgId);
        List<IscUser> age30follow = new ArrayList<IscUser>();
        List<IscUser> age30above40 = new ArrayList<IscUser>();
        List<IscUser> age40above50 = new ArrayList<IscUser>();
        List<IscUser> age51above = new ArrayList<IscUser>();
        LocalDateTime now = LocalDateTime.now();
        for(IscUser iscUser : userList){
            LocalDateTime ageDate = LocalDateTime.parse(iscUser.getBirthDate(), formatter);
            long year = ChronoUnit.YEARS.between(ageDate, now);
            if (year <= 30) {
                age30follow.add(iscUser);
            } else if (year > 30 && year <= 40) {
                age30above40.add(iscUser);
            } else if (year > 40 && year <= 50) {
                age40above50.add(iscUser);
            } else if (year > 50) {
                age51above.add(iscUser);
            }
        }
        BigDecimal allcount = new BigDecimal(age30follow.size() + age30above40.size() + age40above50.size() + age51above.size());
        BigDecimal age30followProp = new BigDecimal(0);
        BigDecimal age30above40Prop = new BigDecimal(0);
        BigDecimal age40above50Prop = new BigDecimal(0);
        BigDecimal age51aboveProp = new BigDecimal(0);
        if (!allcount.equals(BigDecimal.ZERO)) {
            age30followProp = new BigDecimal(age30follow.size()).multiply(new BigDecimal(100)).divide(allcount, 2, BigDecimal.ROUND_HALF_UP);
            age30above40Prop = new BigDecimal(age30above40.size()).multiply(new BigDecimal(100)).divide(allcount, 2, BigDecimal.ROUND_HALF_UP);
            age40above50Prop = new BigDecimal(age40above50.size()).multiply(new BigDecimal(100)).divide(allcount, 2, BigDecimal.ROUND_HALF_UP);
            age51aboveProp = new BigDecimal(age51above.size()).multiply(new BigDecimal(100)).divide(allcount, 2, BigDecimal.ROUND_HALF_UP);
        }

        List<Map<String,Object>> resList = new ArrayList<Map<String,Object>>();
        Map<String,Object> mapAge30follow = assemble(age30follow);
        mapAge30follow.put("name","30岁及以下");
        mapAge30follow.put("bl",String.format("%s%s", age30followProp, "%"));
        resList.add(mapAge30follow);

        Map<String,Object> mapAge30above40 = assemble(age30above40);
        mapAge30above40.put("name","31-40岁");
        mapAge30above40.put("bl",String.format("%s%s", age30above40Prop, "%"));
        resList.add(mapAge30above40);

        Map<String,Object> mapAge40above50 = assemble(age40above50);
        mapAge40above50.put("name","41-50岁");
        mapAge40above50.put("bl",String.format("%s%s", age40above50Prop, "%"));
        resList.add(mapAge40above50);

        Map<String,Object> mapAge51above = assemble(age51above);
        mapAge51above.put("name","51岁及以上");
        mapAge51above.put("bl",String.format("%s%s", age51aboveProp, "%"));
        resList.add(mapAge51above);
        Map<String, Object> resMap = new HashMap<String, Object>();
        resMap.put("series",resList);
        return resMap;
    }

    /**
     * 信息总览-年龄结构分布 - 性别数据组装
     * @param iscUserList
     * @return
     */
    public Map<String,Object> assemble(List<IscUser> iscUserList){
        Map<String,Object> resMap = new HashMap<String,Object>();
        Map<String, List<IscUser>> age30followUseMap = iscUserList.stream().collect(Collectors.groupingBy(IscUser::getSexCode));
        List<HashMap<String,String>> row1 = new ArrayList<>();
        HashMap map11 = new HashMap<String,String>();
        map11.put("sex","男");
        map11.put("count",age30followUseMap.get("1")!=null?age30followUseMap.get("1").size():0);
        row1.add(map11);
        HashMap map12 = new HashMap<String,String>();
        map12.put("sex","女");
        map12.put("count",age30followUseMap.get("2")!=null?age30followUseMap.get("2").size():0);
        row1.add(map12);
        resMap.put("rows",row1);
        resMap.put("value",iscUserList.size());
        return resMap;
    }



    /**
     * 年龄结构分析列表
     *
     * @param orgId
     * @return
     */
    public List<AgeStructureDto> ageStructure(String orgId) {
        List<AgeStructureDto> resList = new ArrayList<AgeStructureDto>();
        List<TOrgDept> orgDeptList = statisticalDao.getDeptsByOrgId(orgId);
        List<IscUser> userList = statisticalDao.searchUserList(orgId);
        Map<String, List<IscUser>> useMap = userList.stream().collect(Collectors.groupingBy(IscUser::getBaseorgId));

        LocalDateTime now = LocalDateTime.now();
        orgDeptList.stream().forEach(item -> {
            AgeStructureDto dto = new AgeStructureDto();
            item.getId();
            List<IscUser> orgUerList = useMap.get(item.getId());

            int age30follow = 0;
            int age30above40 = 0;
            int age40above50 = 0;
            int age51above = 0;
            for (IscUser user : orgUerList) {
                if (StringUtils.isNotBlank(user.getBirthDate())) {
                    LocalDateTime ageDate = LocalDateTime.parse(user.getBirthDate(), formatter);
                    long year = ChronoUnit.YEARS.between(ageDate, now);
                    if (year <= 30) {
                        age30follow++;
                    } else if (year > 30 && year <= 40) {
                        age30above40++;
                    } else if (year > 40 && year <= 50) {
                        age40above50++;
                    } else if (year > 50) {
                        age51above++;
                    }
                }
            }
            BigDecimal allcount = new BigDecimal(age30follow + age30above40 + age40above50 + age51above);
            BigDecimal age30followProp = new BigDecimal(0);
            BigDecimal age30above40Prop = new BigDecimal(0);
            BigDecimal age40above50Prop = new BigDecimal(0);
            BigDecimal age51aboveProp = new BigDecimal(0);
            if (!allcount.equals(BigDecimal.ZERO)) {
                age30followProp = new BigDecimal(age30follow).multiply(new BigDecimal(100)).divide(allcount, 2, BigDecimal.ROUND_HALF_UP);
                age30above40Prop = new BigDecimal(age30above40).multiply(new BigDecimal(100)).divide(allcount, 2, BigDecimal.ROUND_HALF_UP);
                age40above50Prop = new BigDecimal(age40above50).multiply(new BigDecimal(100)).divide(allcount, 2, BigDecimal.ROUND_HALF_UP);
                age51aboveProp = new BigDecimal(age51above).multiply(new BigDecimal(100)).divide(allcount, 2, BigDecimal.ROUND_HALF_UP);
            }
            dto.setOrgid(item.getId());
            dto.setOrgName(item.getName());
            dto.setInStation(age30follow + age30above40 + age40above50 + age51above);
            dto.setAge30follow(age30follow);
            dto.setAge30followProp(String.format("%s%s", age30followProp, "%"));
            dto.setAge30above40(age30above40);
            dto.setAge30above40Prop(String.format("%s%s", age30above40Prop, "%"));
            dto.setAge40above50(age40above50);
            dto.setAge40above50Prop(String.format("%s%s", age40above50Prop, "%"));
            dto.setAge51above(age51above);
            dto.setAge51aboveProp(String.format("%s%s", age51aboveProp, "%"));
            resList.add(dto);
        });
        return resList;
    }


    /**
     * 年龄结构分析图表
     *
     * @param orgId
     * @return
     */
    public HashMap<String, Object> ageStructureChar(String orgId) {
        List<AgeStructureDto> ageStructureDtoList = ageStructure(orgId);

        HashMap<String, Object> resMap = new HashMap<String, Object>();
        List<String> xAxis_data = new ArrayList<String>();
        List<HashMap<String, Object>> series = new ArrayList<HashMap<String, Object>>();


        List<Object> age30follow = new ArrayList<Object>();
        List<Object> age30above40 = new ArrayList<Object>();
        List<Object> age40above50 = new ArrayList<Object>();
        List<Object> age51above = new ArrayList<Object>();

        for (AgeStructureDto dto : ageStructureDtoList) {
            xAxis_data.add(dto.getOrgName());
            HashMap<String, Object> age30followMap = new HashMap<String, Object>();
            age30followMap.put("code", dto.getOrgid());
            age30followMap.put("orgName", dto.getOrgName());
            age30followMap.put("value", dto.getAge30follow());
            age30follow.add(age30followMap);

            HashMap<String, Object> age30above40Map = new HashMap<String, Object>();
            age30above40Map.put("code", dto.getOrgid());
            age30above40Map.put("orgName", dto.getOrgName());
            age30above40Map.put("value", dto.getAge30above40());
            age30above40.add(age30above40Map);

            HashMap<String, Object> age40above50Map = new HashMap<String, Object>();
            age40above50Map.put("code", dto.getOrgid());
            age40above50Map.put("orgName", dto.getOrgName());
            age40above50Map.put("value", dto.getAge40above50());
            age40above50.add(age40above50Map);

            HashMap<String, Object> age51aboveMap = new HashMap<String, Object>();
            age51aboveMap.put("code", dto.getOrgid());
            age51aboveMap.put("orgName", dto.getOrgName());
            age51aboveMap.put("value", dto.getAge51above());
            age51above.add(age51aboveMap);
        }

        HashMap<String, Object> age30followMap = new HashMap<String, Object>();
        age30followMap.put("name", "30岁及以下");
        age30followMap.put("data", age30follow);
        series.add(age30followMap);

        HashMap<String, Object> age30above40Map = new HashMap<String, Object>();
        age30above40Map.put("name", "31-40岁");
        age30above40Map.put("data", age30above40);
        series.add(age30above40Map);


        HashMap<String, Object> age40above50Map = new HashMap<String, Object>();
        age40above50Map.put("name", "41-50岁");
        age40above50Map.put("data", age40above50);
        series.add(age40above50Map);


        HashMap<String, Object> age51aboveMap = new HashMap<String, Object>();
        age51aboveMap.put("name", "51岁及以上");
        age51aboveMap.put("data", age51above);
        series.add(age51aboveMap);

        resMap.put("xAxis_data", xAxis_data);
        resMap.put("series", series);
        return resMap;
    }

    /**
     * 年龄结构分析列表导出
     *
     * @param request
     * @param response
     * @param orgId
     */
    public void exportAgeStructureExcel(HttpServletRequest request, HttpServletResponse response, String orgId) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFCellStyle cellStyle = workbook.createCellStyle(); // 单元格样式
        Font fontStyle = workbook.createFont(); // 字体样式
        fontStyle.setBold(false); // 加粗
//        fontStyle.setFontName("黑体"); // 字体
        fontStyle.setFontHeightInPoints((short) 15); // 大小
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        // 边框，居中
        cellStyle.setWrapText(true);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);


        XSSFSheet sheet = workbook.createSheet("年龄结构分析一览表");
//        sheet.setColumnWidth(0,1000*4);
        sheet.setColumnWidth(1, 1000 * 3);
        sheet.setColumnWidth(2, 1000 * 3);
        sheet.setColumnWidth(3, 1000 * 3);
        sheet.setColumnWidth(4, 1000 * 3);
        sheet.setColumnWidth(5, 1000 * 3);
        sheet.setColumnWidth(6, 1000 * 3);
        sheet.setColumnWidth(7, 1000 * 3);
        sheet.setColumnWidth(8, 1000 * 3);
        sheet.setColumnWidth(9, 1000 * 3);
        XSSFRow row0 = sheet.createRow(0);
        row0.setHeight((short) (12 * 60));
        XSSFCell cellB1 = row0.createCell(0);
        cellB1.setCellValue("年龄结构分析一览表");
        cellB1.setCellStyle(cellStyle);


        XSSFRow row1 = sheet.createRow(1);
        row1.setHeight((short) (12 * 50));
        XSSFCell cellB2 = row1.createCell(0);
        cellB2.setCellValue("地区");
        cellB2.setCellStyle(cellStyle);
        XSSFCell cellB3 = row1.createCell(1);
        cellB3.setCellValue("在岗人数");
        cellB3.setCellStyle(cellStyle);
        XSSFCell cellB4 = row1.createCell(2);
        cellB4.setCellValue("30岁及以下");
        cellB4.setCellStyle(cellStyle);
        XSSFCell cellB5 = row1.createCell(4);
        cellB5.setCellValue("31-40岁");
        cellB5.setCellStyle(cellStyle);
        XSSFCell cellB6 = row1.createCell(6);
        cellB6.setCellValue("41-50岁");
        cellB6.setCellStyle(cellStyle);
        XSSFCell cellB7 = row1.createCell(8);
        cellB7.setCellValue("51岁及以上");
        cellB7.setCellStyle(cellStyle);

        XSSFRow row2 = sheet.createRow(2);
        row2.setHeight((short) (12 * 60));
        XSSFCell cellB22 = row2.createCell(2);
        cellB22.setCellValue("人数");
        cellB22.setCellStyle(cellStyle);
        XSSFCell cellB23 = row2.createCell(3);
        cellB23.setCellValue("占比");
        cellB23.setCellStyle(cellStyle);
        XSSFCell cellB24 = row2.createCell(4);
        cellB24.setCellValue("人数");
        cellB24.setCellStyle(cellStyle);
        XSSFCell cellB25 = row2.createCell(5);
        cellB25.setCellValue("占比");
        cellB25.setCellStyle(cellStyle);
        XSSFCell cellB26 = row2.createCell(6);
        cellB26.setCellValue("人数");
        cellB26.setCellStyle(cellStyle);
        XSSFCell cellB27 = row2.createCell(7);
        cellB27.setCellValue("占比");
        cellB27.setCellStyle(cellStyle);
        XSSFCell cellB28 = row2.createCell(8);
        cellB28.setCellValue("人数");
        cellB28.setCellStyle(cellStyle);
        XSSFCell cellB29 = row2.createCell(9);
        cellB29.setCellValue("占比");
        cellB29.setCellStyle(cellStyle);

        // 合并单元格
        CellRangeAddress cra1 = new CellRangeAddress(0, 0, 0, 9); // 起始行, 终止行, 起始列, 终止列
        CellRangeAddress cra2 = new CellRangeAddress(1, 2, 0, 0); // 起始行, 终止行, 起始列, 终止列
        CellRangeAddress cra3 = new CellRangeAddress(1, 2, 1, 1); // 起始行, 终止行, 起始列, 终止列
        CellRangeAddress cra4 = new CellRangeAddress(1, 1, 2, 3); // 起始行, 终止行, 起始列, 终止列
        CellRangeAddress cra5 = new CellRangeAddress(1, 1, 4, 5); // 起始行, 终止行, 起始列, 终止列
        CellRangeAddress cra6 = new CellRangeAddress(1, 1, 6, 7); // 起始行, 终止行, 起始列, 终止列
        CellRangeAddress cra7 = new CellRangeAddress(1, 1, 8, 9); // 起始行, 终止行, 起始列, 终止列
        sheet.addMergedRegion(cra1);
        sheet.addMergedRegion(cra2);
        sheet.addMergedRegion(cra3);
        sheet.addMergedRegion(cra4);
        sheet.addMergedRegion(cra5);
        sheet.addMergedRegion(cra6);
        sheet.addMergedRegion(cra7);

        List<CellRangeAddress> cellRangeList = new ArrayList<CellRangeAddress>();
        cellRangeList.add(cra1);
        cellRangeList.add(cra2);
        cellRangeList.add(cra3);
        cellRangeList.add(cra4);
        cellRangeList.add(cra5);
        cellRangeList.add(cra6);
        cellRangeList.add(cra7);
        cellRangeList.forEach(item -> {
            RegionUtil.setBorderBottom(1, item, sheet); // 下边框
            RegionUtil.setBorderLeft(1, item, sheet); // 左边框
            RegionUtil.setBorderRight(1, item, sheet); // 有边框
            RegionUtil.setBorderTop(1, item, sheet); // 上边框
        });


        List<AgeStructureDto> ageStructureDtoList = ageStructure(orgId);
        int i = 3;
        for (AgeStructureDto dto : ageStructureDtoList) {
            XSSFRow row = sheet.createRow(i);
            row.setHeight((short) (13 * 60));
            XSSFCell cell0 = row.createCell(0);
            cell0.setCellValue(dto.getOrgName());
            cell0.setCellStyle(cellStyle);
            XSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(dto.getInStation());
            cell1.setCellStyle(cellStyle);
            XSSFCell cell2 = row.createCell(2);
            cell2.setCellValue(dto.getAge30follow());
            cell2.setCellStyle(cellStyle);
            XSSFCell cell3 = row.createCell(3);
            cell3.setCellValue(dto.getAge30followProp());
            cell3.setCellStyle(cellStyle);
            XSSFCell cell4 = row.createCell(4);
            cell4.setCellValue(dto.getAge30above40());
            cell4.setCellStyle(cellStyle);
            XSSFCell cell5 = row.createCell(5);
            cell5.setCellValue(dto.getAge30above40Prop());
            cell5.setCellStyle(cellStyle);
            XSSFCell cell6 = row.createCell(6);
            cell6.setCellValue(dto.getAge40above50());
            cell6.setCellStyle(cellStyle);
            XSSFCell cell7 = row.createCell(7);
            cell7.setCellValue(dto.getAge40above50Prop());
            cell7.setCellStyle(cellStyle);
            XSSFCell cell8 = row.createCell(8);
            cell8.setCellValue(dto.getAge51above());
            cell8.setCellStyle(cellStyle);
            XSSFCell cell9 = row.createCell(9);
            cell9.setCellValue(dto.getAge51aboveProp());
            cell9.setCellStyle(cellStyle);
            i++;
        }
        sheet.autoSizeColumn((short) 0);
        TOrgDept org = statisticalDao.getDeptsById(orgId);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String fileName = "年龄结构分析" + sdf.format(new Date());
        if (org != null) {
            fileName = org.getName() + fileName;
        }
        exportExcelBrowser(request, response, fileName, workbook);
    }

    /**
     * 向页面导出excel
     *
     * @param request
     * @param response
     * @param fileName 文件名称
     * @param workbook 工作簿
     */
    private void exportExcelBrowser(HttpServletRequest request, HttpServletResponse response, String fileName, XSSFWorkbook workbook) {
        OutputStream os = null;
        try {
            ExcelExportUtil.setResponseHeader(request, response, fileName);
            os = response.getOutputStream();
            workbook.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                try {
                    os.flush();
                    os.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 信息总览-性别分布
     * @param orgId
     * @return
     */
    public HashMap<String, Object> sexDistribution(String orgId) {
        List<HashMap> mapList = statisticalDao.getGroupBySex(orgId);
        Object boy = "";
        Object girl = "";
        if (CollectionUtils.isNotEmpty(mapList)) {
            for (HashMap item : mapList) {
                for (Object key : item.keySet()) {
                    if ("1".equals(item.get(key))) {
                        item.put("sex", "男");
                        boy = item.get("count");
                    } else if ("2".equals(item.get(key))) {
                        girl = item.get("count");
                        item.put("sex", "女");
                    }
                }
            }
        }
        HashMap resmap = new HashMap<String, Object>();
        resmap.put("series", mapList);
        resmap.put("total", Integer.valueOf(boy.toString()) + Integer.valueOf(girl.toString()));
        return resmap;
    }

    /**
     * 信息总览-学历分布
     * @param orgId
     * @return
     */
    @Override
    public HashMap<String, Object> aChartEducation(String orgId) {
        List<TEducateInfo> educateList = statisticalDao.searchEducate(orgId);
        /**
         * 1.中专及以下 ^(([4-9]\d))$
         * 2.大专 ^(([3]\d))$
         * 3.本科 ^(([2]\d))$
         * 4.硕士及以上 ^(([1]\d))$
         */
        int polytechnic = 0;
        int junior = 0;
        int undergraduate = 0;
        int master = 0;

        final Pattern pattern1 = Pattern.compile("^(([4-9]\\d))$");
        final Pattern pattern2 = Pattern.compile("^(([3]\\d))$");
        final Pattern pattern3 = Pattern.compile("^(([2]\\d))$");
        final Pattern pattern4 = Pattern.compile("^(([1]\\d))$");
        for(TEducateInfo ducateInfo :educateList){
            if(pattern1.matcher(ducateInfo.getEducName()).matches()){
                polytechnic++;
            }else if(pattern2.matcher(ducateInfo.getEducName()).matches()){
                junior++;
            }else if(pattern3.matcher(ducateInfo.getEducName()).matches()){
                undergraduate++;
            }else if(pattern4.matcher(ducateInfo.getEducName()).matches()){
                master++;
            }
        }
        List<HashMap<String,String>> reList = new ArrayList<HashMap<String,String>>();
        HashMap<String,String> map1 = new HashMap<String,String>();
        map1.put("name","中专及以下");
        map1.put("value",String.valueOf(polytechnic));
        reList.add(map1);

        HashMap<String,String> map2 = new HashMap<String,String>();
        map2.put("name","大专");
        map2.put("value",String.valueOf(junior));
        reList.add(map2);

        HashMap<String,String> map3 = new HashMap<String,String>();
        map3.put("name","本科");
        map3.put("value",String.valueOf(undergraduate));
        reList.add(map3);

        HashMap<String,String> map4 = new HashMap<String,String>();
        map4.put("name","硕士及以上");
        map4.put("value",String.valueOf(master));
        reList.add(map4);

        HashMap<String, Object> resMap = new HashMap<>();
        resMap.put("series",reList);
        return resMap;
    }

    /**
     * 信息总览-退休趋势分析
     * @param orgId
     * @return
     */
    @Override
    public HashMap<String , Object>  aChartRetire(String orgId) {
        LocalDateTime now = LocalDateTime.now();
        LocalDateTime next1 = now.plus(1,ChronoUnit.YEARS);
        LocalDateTime next2 = now.plus(2,ChronoUnit.YEARS);
        LocalDateTime next3 = now.plus(3,ChronoUnit.YEARS);
        LocalDateTime next4 = now.plus(4,ChronoUnit.YEARS);

        List<IscUser> userList = statisticalDao.searchUserList(orgId);
        userList = userList.stream().filter(p -> p.getBirthDate()!=null).filter(p -> ChronoUnit.YEARS.between( LocalDateTime.parse(p.getBirthDate(), formatter), now)>womanRetireAge-5).collect(Collectors.toList());
        Map<String, List<IscUser>> useMap = userList.stream().collect(Collectors.groupingBy(IscUser::getSexCode));
        List<IscUser> manUser = useMap.get("1");
        List<IscUser> womanUser = useMap.get("2");
        int man60 = manUser.stream().filter(p -> ChronoUnit.YEARS.between( LocalDateTime.parse(p.getBirthDate(), formatter), now)>=manRetireAge).collect(Collectors.toList()).size();
        int man59 = manUser.stream().filter(p -> ChronoUnit.YEARS.between( LocalDateTime.parse(p.getBirthDate(), formatter), next1)==manRetireAge).collect(Collectors.toList()).size();
        int man58 = manUser.stream().filter(p -> ChronoUnit.YEARS.between( LocalDateTime.parse(p.getBirthDate(), formatter), next2)==manRetireAge).collect(Collectors.toList()).size();
        int man57 = manUser.stream().filter(p -> ChronoUnit.YEARS.between( LocalDateTime.parse(p.getBirthDate(), formatter), next3)==manRetireAge).collect(Collectors.toList()).size();
        int man56 = manUser.stream().filter(p -> ChronoUnit.YEARS.between( LocalDateTime.parse(p.getBirthDate(), formatter), next4)==manRetireAge).collect(Collectors.toList()).size();
        List<Integer> manList = new ArrayList<Integer>();
        manList.add(man60);
        manList.add(man59);
        manList.add(man58);
        manList.add(man57);
        manList.add(man56);

        int woman55 = womanUser.stream().filter(p -> ChronoUnit.YEARS.between( LocalDateTime.parse(p.getBirthDate(), formatter), now)>=womanRetireAge).collect(Collectors.toList()).size();
        int woman54 = womanUser.stream().filter(p -> ChronoUnit.YEARS.between( LocalDateTime.parse(p.getBirthDate(), formatter), next1)==womanRetireAge).collect(Collectors.toList()).size();
        int woman53 = womanUser.stream().filter(p -> ChronoUnit.YEARS.between( LocalDateTime.parse(p.getBirthDate(), formatter), next2)==womanRetireAge).collect(Collectors.toList()).size();
        int woman52 = womanUser.stream().filter(p -> ChronoUnit.YEARS.between( LocalDateTime.parse(p.getBirthDate(), formatter), next3)==womanRetireAge).collect(Collectors.toList()).size();
        int woman51 = womanUser.stream().filter(p -> ChronoUnit.YEARS.between( LocalDateTime.parse(p.getBirthDate(), formatter), next4)==womanRetireAge).collect(Collectors.toList()).size();
        List<Integer> womanList = new ArrayList<Integer>();
        womanList.add(woman55);
        womanList.add(woman54);
        womanList.add(woman53);
        womanList.add(woman52);
        womanList.add(woman51);


        List<Integer> xList = new ArrayList<Integer>();
        xList.add(now.getYear());
        xList.add(next1.getYear());
        xList.add(next2.getYear());
        xList.add(next3.getYear());
        xList.add(next4.getYear());


        List<Object> seriesList = new ArrayList<Object>();

        Map<String , Object> map1 = new HashMap<String , Object>();
        map1.put("sex","男");
        map1.put("value",manList);
        seriesList.add(map1);

        Map<String , Object> map2 = new HashMap<String , Object>();
        map2.put("sex","女");
        map2.put("value",womanList);
        seriesList.add(map2);

        HashMap<String , Object> resMap = new HashMap<String , Object>();
        resMap.put("xAxis_data",xList);
        resMap.put("series",seriesList);
        return resMap;
    }

    /**
     * 信息总览-职称分布
     * @param orgId
     * @return
     */
    @Override
    public HashMap<String, Object> aChartTitle(String orgId) {
        List<TSkillLevelInfo> skillLevelList = statisticalDao.searchSkillLevel(orgId);
        Map<String,List<TSkillLevelInfo>> groupMap = skillLevelList.stream().filter(p ->p.getQualifiLevel()!=null).collect(Collectors.groupingBy(TSkillLevelInfo::getQualifiLevel));
        List<TSysDictionaryInfo> dictList = sysDictionaryInfoDao.getDictionarys("QualificationLevel");
        List<Object> reslist = new ArrayList();
        for (TSysDictionaryInfo dic:dictList){
            Map<String,Object> map = new HashMap();
            map.put("name",dic.getName());
            map.put("value",groupMap.get(dic.getValue()).size());
            reslist.add(map);
        }
        HashMap<String, Object> resMap = new HashMap<>();
        resMap.put("series",reslist);
        return resMap;
    }

    /**
     * 信息总览-干部分布
     * @param orgId
     * @return
     */
    @Override
    public HashMap<String, Object> aChartCadre(String orgId) {
        /**
         * 01-07 处级及以上
         * 08 副处级
         * 09 正科
         * 10 副科级
         * 11 - 其它 暂时不列入领导干部
         */
        List<IscUser> cadreUserList = statisticalDao.searchCadreUserList(orgId);
        List<IscUser> cadre07Lst = new ArrayList<IscUser>();
        List<IscUser> cadre08Lst = new ArrayList<IscUser>();
        List<IscUser> cadre09Lst = new ArrayList<IscUser>();
        List<IscUser> cadre10Lst = new ArrayList<IscUser>();

        for(IscUser user:cadreUserList){
            Integer postLever = null;
            if(StringUtils.isNotBlank(user.getPostLevel())){
                postLever = Integer.valueOf(user.getPostLevel());
            }
            if(postLever<8){
                cadre07Lst.add(user);
            }else if(postLever==8){
                cadre08Lst.add(user);
            }else if(postLever==9){
                cadre09Lst.add(user);
            }else if(postLever==10){
                cadre10Lst.add(user);
            }
        }

        List<String> yAxis_data = new ArrayList<String>();
        yAxis_data.add("处级及以上");
        yAxis_data.add("副处级");
        yAxis_data.add("正科");
        yAxis_data.add("副科级");

        List<Object> series = new ArrayList<Object>();
        Map<String,Object> map07 = aChartCadreAssemble(cadre07Lst);
        map07.put("name","处级及以上");
        series.add(map07);

        Map<String,Object> map08 = aChartCadreAssemble(cadre08Lst);
        map08.put("name","副处级");
        series.add(map08);

        Map<String,Object> map09 = aChartCadreAssemble(cadre09Lst);
        map09.put("name","正科");
        series.add(map09);

        Map<String,Object> map10 = aChartCadreAssemble(cadre10Lst);
        map10.put("name","副科级");
        series.add(map10);


        HashMap<String,Object> resMap = new HashMap<>();
        resMap.put("yAxis_data",yAxis_data);
        resMap.put("series",series);
        return resMap;
    }


    /**
     * 信息总览-干部分布 - 数据组装
     * @param userList
     * @return
     */
    public Map<String,Object> aChartCadreAssemble(List<IscUser> userList){

        List<IscUser> age30follow = new ArrayList<IscUser>();
        List<IscUser> age30above40 = new ArrayList<IscUser>();
        List<IscUser> age40above50 = new ArrayList<IscUser>();
        List<IscUser> age51above = new ArrayList<IscUser>();
        LocalDateTime now = LocalDateTime.now();
        for(IscUser iscUser : userList){
            LocalDateTime ageDate = LocalDateTime.parse(iscUser.getBirthDate(), formatter);
            long year = ChronoUnit.YEARS.between(ageDate, now);
            if (year <= 30) {
                age30follow.add(iscUser);
            } else if (year > 30 && year <= 40) {
                age30above40.add(iscUser);
            } else if (year > 40 && year <= 50) {
                age40above50.add(iscUser);
            } else if (year > 50) {
                age51above.add(iscUser);
            }
        }
        HashMap<String,Object> row = null;
        List<Object> rows = new ArrayList<>();
        if(age30follow.size()>0){
            row = new  HashMap<String,Object>();
            row.put("name","30岁及以下");
            row.put("value",age30follow.size());
            rows.add(row);
        }
        if(age30above40.size()>0){
            row = new  HashMap<String,Object>();
            row.put("name","31-40岁");
            row.put("value",age30above40.size());
            rows.add(row);
        }
        if(age40above50.size()>0){
            row = new  HashMap<String,Object>();
            row.put("name","41-50岁");
            row.put("value",age40above50.size());
            rows.add(row);
        }
        if(age51above.size()>0){
            row = new  HashMap<String,Object>();
            row.put("name","51岁及以上");
            row.put("value",age51above.size());
            rows.add(row);
        }

        HashMap<String,Object> resMap = new HashMap<>();
        resMap.put("rows",rows);
        resMap.put("value",userList.size());
        return resMap;
    }

    /**
     * 职员职级-图表
     * @param orgId
     * @return
     */
    @Override
    public HashMap<String, Object> employeeLevelChar(String orgId) {
        List<HashMap> mapList = statisticalDao.getGroupByEmployeeLevel(orgId);

        List<String> xAxis_data = new ArrayList<>();
        List<Object> data = new ArrayList<>();
        for(HashMap hashMap:mapList){
            xAxis_data.add(String.valueOf(hashMap.get("level")));
            HashMap<String,Object> map = new HashMap<String,Object>();
            map.put("code",hashMap.get("level"));
            map.put("value",hashMap.get("count"));
            data.add(map);
        }

        HashMap<String,Object> seriesmap = new HashMap<String,Object>();
        seriesmap.put("data",data);
        seriesmap.put("name","人数");
        List<Object> series = new ArrayList<>();
        series.add(seriesmap);
        HashMap<String, Object> resMap = new HashMap<>();
        resMap.put("xAxis_data",xAxis_data);
        resMap.put("series",series);
        return resMap;
    }


    @Override
    public void employeeLevel(Page<StaffRankDto> page, StaffRankDto staffRankDto) {
        statisticalDao.findEmployeeLevelList(page,staffRankDto);
        page.getResult().stream().forEach(item ->{
            item.setEmployeeLevel(SysDictionaryUtil.getNameByValueForDefault("StaffLevel", item.getEmployeeLevel()));
            item.setSexName(SysDictionaryUtil.getNameByValueForDefault("Gender", item.getSex()));
            item.setContEduc(SysDictionaryUtil.getNameByValueForDefault("PersonnelQualifications", item.getContEduc()));
            item.setHighestEduc(SysDictionaryUtil.getNameByValueForDefault("PersonnelQualifications", item.getHighestEduc()));
            if(StringUtils.isNotBlank(item.getSubjName())){
                item.setSubjName(item.getSubjName().split("\\|")[1]);
            }
        });
    }

    @Override
    public List<StaffRankDto> findEmployeeLevelList(StaffRankDto staffRankDto){
        List<StaffRankDto> res = statisticalDao.findEmployeeLevelList(staffRankDto);
        res.stream().forEach(item ->{
            item.setEmployeeLevel(SysDictionaryUtil.getNameByValueForDefault("StaffLevel", item.getEmployeeLevel()));
            item.setSexName(SysDictionaryUtil.getNameByValueForDefault("Gender", item.getSex()));
            item.setContEduc(SysDictionaryUtil.getNameByValueForDefault("PersonnelQualifications", item.getContEduc()));
            item.setHighestEduc(SysDictionaryUtil.getNameByValueForDefault("PersonnelQualifications", item.getHighestEduc()));
            if(StringUtils.isNotBlank(item.getSubjName())){
                item.setSubjName(item.getSubjName().split("\\|")[1]);
            }
        });
        return res;
    }

    /**
     * 职员职级-表格-导出报表
     * @param request
     * @param response
     * @param staffRankDto
     */
    @Override
    public void exportemployeeLevelExcel(HttpServletRequest request, HttpServletResponse response,StaffRankDto staffRankDto){
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFCellStyle cellStyle = workbook.createCellStyle(); // 单元格样式
        Font fontStyle = workbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
//        fontStyle.setFontName("黑体"); // 字体
        fontStyle.setFontHeightInPoints((short) 15); // 大小
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        // 边框，居中
        cellStyle.setWrapText(true);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);

        XSSFCellStyle cellStyle2 = workbook.createCellStyle(); // 单元格样式
        // 边框，居中
        cellStyle2.setWrapText(true);
        cellStyle2.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle2.setBorderBottom(BorderStyle.THIN);
        cellStyle2.setBorderLeft(BorderStyle.THIN);
        cellStyle2.setBorderRight(BorderStyle.THIN);
        cellStyle2.setBorderTop(BorderStyle.THIN);

        XSSFSheet sheet = workbook.createSheet("职员职级一览表");
        sheet.setColumnWidth(0,1000 * 3);
        sheet.setColumnWidth(1, 1000 * 5);
        sheet.setColumnWidth(2, 1000 * 2);
        sheet.setColumnWidth(3, 1000 * 3);
        sheet.setColumnWidth(4, 1000 * 3);
        sheet.setColumnWidth(5, 1000 * 3);
        sheet.setColumnWidth(6, 1000 * 4);
        sheet.setColumnWidth(7, 1000 * 3);
        sheet.setColumnWidth(8, 1000 * 3);
        sheet.setColumnWidth(9, 1000 * 3);
        XSSFRow row0 = sheet.createRow(0);
        row0.setHeight((short) (12 * 60));
        XSSFCell cellB1 = row0.createCell(0);
        cellB1.setCellValue("职员职级一览表");
        cellB1.setCellStyle(cellStyle);

        XSSFRow row1 = sheet.createRow(1);
        row1.setHeight((short) (12 * 50));
        XSSFCell cellB10 = row1.createCell(0);
        cellB10.setCellValue("姓名");
        cellB10.setCellStyle(cellStyle);
        XSSFCell cellB11 = row1.createCell(1);
        cellB11.setCellValue("工作单位");
        cellB11.setCellStyle(cellStyle);
        XSSFCell cellB12 = row1.createCell(2);
        cellB12.setCellValue("性别");
        cellB12.setCellStyle(cellStyle);
        XSSFCell cellB13 = row1.createCell(3);
        cellB13.setCellValue("出生日期");
        cellB13.setCellStyle(cellStyle);
        XSSFCell cellB14 = row1.createCell(4);
        cellB14.setCellValue("职员职级");
        cellB14.setCellStyle(cellStyle);
        XSSFCell cellB15 = row1.createCell(5);
        cellB15.setCellValue("技术职称");
        cellB15.setCellStyle(cellStyle);
        XSSFCell cellB16 = row1.createCell(6);
        cellB16.setCellValue("全日制学历");
        cellB16.setCellStyle(cellStyle);
        XSSFCell cellB17 = row1.createCell(7);
        cellB17.setCellValue("毕业院校");
        cellB17.setCellStyle(cellStyle);
        XSSFCell cellB18 = row1.createCell(8);
        cellB18.setCellValue("最高学历");
        cellB18.setCellStyle(cellStyle);
        XSSFCell cellB19 = row1.createCell(9);
        cellB19.setCellValue("毕业院校");
        cellB19.setCellStyle(cellStyle);

        // 合并单元格
        CellRangeAddress cra1 = new CellRangeAddress(0, 0, 0, 9); // 起始行, 终止行, 起始列, 终止列
        sheet.addMergedRegion(cra1);
        RegionUtil.setBorderBottom(1, cra1, sheet); // 下边框
        RegionUtil.setBorderLeft(1, cra1, sheet); // 左边框
        RegionUtil.setBorderRight(1, cra1, sheet); // 有边框
        RegionUtil.setBorderTop(1, cra1, sheet); // 上边框

        List<StaffRankDto> staffRankDtoList = findEmployeeLevelList(staffRankDto);
        int i = 2;
        for (StaffRankDto dto : staffRankDtoList) {
            XSSFRow row = sheet.createRow(i);
            row.setHeight((short) (13 * 60));
            XSSFCell cell0 = row.createCell(0);
            cell0.setCellValue(dto.getUserName());
            cell0.setCellStyle(cellStyle2);
            XSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(dto.getOrgName());
            cell1.setCellStyle(cellStyle2);
            XSSFCell cell2 = row.createCell(2);
            cell2.setCellValue(dto.getSexName());
            cell2.setCellStyle(cellStyle2);
            XSSFCell cell3 = row.createCell(3);
            cell3.setCellValue(DateFormatUtils.format(dto.getBirthDate(),"yyyy-MM-dd"));
            cell3.setCellStyle(cellStyle2);
            XSSFCell cell4 = row.createCell(4);
            cell4.setCellValue(dto.getEmployeeLevel());
            cell4.setCellStyle(cellStyle2);
            XSSFCell cell5 = row.createCell(5);
            cell5.setCellValue(dto.getSubjName());
            cell5.setCellStyle(cellStyle2);
            XSSFCell cell6 = row.createCell(6);
            cell6.setCellValue(dto.getContEduc());
            cell6.setCellStyle(cellStyle2);
            XSSFCell cell7 = row.createCell(7);
            cell7.setCellValue(dto.getContEducSchool());
            cell7.setCellStyle(cellStyle2);
            XSSFCell cell8 = row.createCell(8);
            cell8.setCellValue(dto.getHighestEduc());
            cell8.setCellStyle(cellStyle2);
            XSSFCell cell9 = row.createCell(9);
            cell9.setCellValue(dto.getHighestEducSchool());
            cell9.setCellStyle(cellStyle2);
            i++;
        }
        sheet.autoSizeColumn((short) 0);
        TOrgDept org = statisticalDao.getDeptsById(staffRankDto.getOrgId());
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String fileName = "职员职级一览表" + sdf.format(new Date());
        if (org != null) {
            fileName = org.getName() + fileName;
        }
        exportExcelBrowser(request, response, fileName, workbook);
    }
}
